package com.prs.crm.dao.hr.impl;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Properties;

import org.apache.struts2.components.Bean;
import org.apache.struts2.convention.annotation.Result;
import org.hibernate.Criteria;
import org.hibernate.FetchMode;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.springframework.orm.hibernate3.HibernateCallback;

import sun.java2d.pipe.SpanShapeRenderer.Simple;

import com.prs.crm.context.AppContext;
import com.prs.crm.dao.hr.PersonDao;
import com.prs.crm.dao.impl.BaseDaoImpl;
import com.prs.crm.domain.hr.Dept;
import com.prs.crm.domain.hr.Person;
import com.prs.crm.domain.hr.Role;
import com.prs.crm.domain.hr.ShareEim;
import com.prs.crm.service.person.ShareEimService;

public class PersonDaoImpl extends BaseDaoImpl<Person> implements PersonDao{
	private Connection con;
	private Statement stmt;
	private Connection con1;
	private Statement stmt1;
	private ResultSet rs;
	private ResultSet rs1;
	private ResultSet rs2;
	private ResultSet rs3;
	private ResultSet rs4;
	private Statement stmt2;
	private Statement stmt3;
	private Statement stmt4;
	private Statement stmt5;
	private Statement stmt6;
	private Statement stmt7;
	private ShareEimService shareEimService;
	private String ClassName;
	private String userName;
	private String userPass;
	private String url1;
	private String address;
	private String baseName;
	public ShareEimService getShareEimService() {
		return shareEimService;
	}

	public void setShareEimService(ShareEimService shareEimService) {
		this.shareEimService = shareEimService;
	}

	@SuppressWarnings("unchecked")
	public Collection<Person> findAll()
	{
		return this.getHibernateTemplate().loadAll(Person.class);
	}
	
	@SuppressWarnings("unchecked")
	public 	Collection<Person> findByDept(final Dept dept)
	{
		return (Collection<Person>)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.add(Restrictions.eq("dept",dept));
				         	criteria.addOrder(Order.asc("id"));
				        	return criteria.list();
				        }   
				 } );
	}
	
	@SuppressWarnings("unchecked")
	public Person findByCode(final String code)
	{
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
	
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				    
				        	criteria.add(Restrictions.eq("loginCode",code));
				        	List result = criteria.list();
				        	if (result.size()>0)
				        		return result.get(0);
				        	else
				        		return null;
				        }   
				 } );
	}
	
	@SuppressWarnings("unchecked")
	public Collection<Person> findByDeptRole(final Dept dept,final Role role)
	{
		return (Collection<Person>)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.add(Restrictions.eq("role",role));
				        	criteria.add(Restrictions.eq("dept",dept));
				        	return criteria.list();
				        }   
				 } );
	}
	
	@SuppressWarnings("unchecked")
	public Collection<Person> findPersons(final Collection<Integer> ids)
	{
		if (ids.size()>0)
		{
			return (Collection<Person>)this.getHibernateTemplate().execute(
				      new HibernateCallback() {   
							public Object doInHibernate(Session session) throws HibernateException {
					        	Criteria criteria = session.createCriteria(Person.class);
					        	criteria.add(Restrictions.in("id", ids));
					        	return criteria.list();
					        }   
					 } );
		}
		else
		{
			return null;
		}
	}
	
	@Override
	public Person findByUserName(final String username)
	{
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						@SuppressWarnings("unchecked")
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.createAlias("resume", "r");
				        	criteria.add(Restrictions.eq("r.name", username));
				        	List<Person> result = criteria.list();
				        	return (result.size()>0)?result.get(0):null;
				        }   
				 } );
	}
	@Override
	public Person  fuzzyFindByUserName(final String username)
	{
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						@SuppressWarnings("unchecked")
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.createAlias("resume", "r");
				        	criteria.add(Restrictions.like("r.name", "%"+username+"%"));
				        	List<Person> result = criteria.list();
				        	return (result.size()>0)?result.get(0):null;
				        }   
				 } );
	}
	@Override
	public Person findNext(final Person person)
	{
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						@SuppressWarnings("unchecked")
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.add(Restrictions.gt("id", person.getId()));
				        	criteria.addOrder(Order.asc("id"));
				        	List<Person> result = criteria.setMaxResults(1).list();
				        	return (result.size()>0)?result.get(0):null;
				        }   
				 } );
	}
	
	public Person findFirstPerson()
	{
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						@SuppressWarnings("unchecked")
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.addOrder(Order.asc("id"));
				        	List<Person> result = criteria.setMaxResults(1).list();
				        	return (result.size()>0)?result.get(0):null;
				        }   
				 } );
	}
	
	public 	Person findPrev(final Person person)
	{
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						@SuppressWarnings("unchecked")
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);

				        	criteria.add(Restrictions.lt("id", person.getId()));
				        	criteria.addOrder(Order.desc("id"));
				        	List<Person> result = criteria.setMaxResults(1).list();
				        	return (result.size()>0)?result.get(0):null;
				        }   
				 } );	
	}

	@SuppressWarnings("unchecked")
	@Override
	public Collection<Person>  getAllPerson() {
		return (Collection<Person>) this.getHibernateTemplate().execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session arg0)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Criteria criteria = arg0.createCriteria(Person.class);
				return criteria.list();
			}
			
		});
	}

	@Override
	public Collection<?> getReportPersonRoleSource() {
		// TODO Auto-generated method stub
		return (Collection<?>) this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session arg0)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				
				Criteria criteria = arg0.createCriteria(Person.class);
				ProjectionList projectionlist=Projections.projectionList();  
	        	projectionlist.add(Projections.groupProperty("role"));
	        	projectionlist.add(Projections.rowCount());
	        	criteria.setProjection(projectionlist);
				return criteria.list();
			}
			
		});
	}

	@Override
	public Collection<?> getReportPersonDeptSource() {
		// TODO Auto-generated method stub
		return (Collection<?>) this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session arg0)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				
				Criteria criteria = arg0.createCriteria(Person.class);
				ProjectionList projectionlist=Projections.projectionList();  
	        	projectionlist.add(Projections.groupProperty("dept"));
	        	projectionlist.add(Projections.rowCount());
	        	criteria.setProjection(projectionlist);
				return criteria.list();
			}
			
		});
	}

	@Override
	public Collection<?> getReportPersonJobTitleSource() {
		// TODO Auto-generated method stub
		return (Collection<?>) this.getHibernateTemplate().execute(new HibernateCallback(){

			@Override
			public Object doInHibernate(Session arg0)
					throws HibernateException, SQLException {
				// TODO Auto-generated method stub
				Criteria criteria = arg0.createCriteria(Person.class);
				ProjectionList projectionlist=Projections.projectionList();
				criteria.createAlias("resume","rs");
	        	projectionlist.add(Projections.groupProperty("rs.title"));
	        	projectionlist.add(Projections.rowCount());
	        	criteria.setProjection(projectionlist);
	        	return criteria.list();
			}
			
		});
	} 
	@Override
	public List<Person> getAllPersons() {
		return (List<Person>)this.getHibernateTemplate().execute(
				new HibernateCallback()
				{
					public Object doInHibernate(Session session) throws HibernateException {
						
				Criteria criteria = session.createCriteria(Person.class).setFetchMode("transferDeclare.shipOwner",FetchMode.JOIN);
				return criteria.list();
					}
			  }
				
		);
}

	@Override
	public void removePerson(Person p) {
		
		this.getHibernateTemplate().delete(p);
	}

	@Override
	public List<Person> getNoticePerson(final Role role) {
		return (List<Person>)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.add(Restrictions.eq("role",role));
				        	return criteria.list();
				        }   
				 } );
	}

	@SuppressWarnings("unchecked")
	@Override
	public Person getByroot(final String name) {
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	criteria.add(Restrictions.eq("loginCode",name));
				        	List<Person>  kk=criteria.list();
				        	if(kk.size()==0){
				        		return null;
				        	}else{
				        	return kk.get(0);
				        	}
				        }   
				 } );
	}

	@Override
	public void saveOrUpdate(Person person) {
		// TODO Auto-generated method stub
		this.getHibernateTemplate().saveOrUpdate(person);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public Person getPersonByMovie(final Integer  movieId, final String userStyle) {
		// TODO Auto-generated method stub
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	
				        	criteria.createAlias("movie", "m");
				        	criteria.add(Restrictions.eq("m.id", movieId));
				        	criteria.add(Restrictions.eq("userStyle",userStyle));
				        	
				        	List<Person>  kk=criteria.list();
				        	if(kk.size()==0){
				        		return null;
				        	}else{
				        	return kk.get(0);
				        	}
				        }   
				 } );
	}

	@SuppressWarnings("unchecked")
	@Override
	public Person getPersonByTicketer(final Integer ticketerId, final String userStyle) {
		// TODO Auto-generated method stub
		return (Person)this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						public Object doInHibernate(Session session) throws HibernateException {
				        	Criteria criteria = session.createCriteria(Person.class);
				        	
				        	criteria.createAlias("ticketer", "t");
				        	criteria.add(Restrictions.eq("t.id", ticketerId));
				        	criteria.add(Restrictions.eq("userStyle",userStyle));
				        	
				        	List<Person>  kk=criteria.list();
				        	if(kk.size()==0){
				        		return null;
				        	}else{
				        	return kk.get(0);
				        	}
				        }   
				 } );
	}

	@Override
	public Integer delByPerson() {
		return (Integer) this.getHibernateTemplate().execute(
			      new HibernateCallback() {   
						public Object doInHibernate(Session session) throws HibernateException {
							Query query = session.createQuery("delete from Person where companys!=null or movie!=null or ticketer!=null");
							return query.executeUpdate();
				        }   
				 } );
	}

	@Override
	public Person getPersonById(final Integer id) {
		Criteria criteria = this.getSession().createCriteria(Person.class);   	
		criteria.add(Restrictions.eq("id", id));
		List kk=criteria.list();
    	if(kk.size()==0 || kk==null){
    		return null;
    	}else{
    		return (Person) kk.get(0);
    	}
	}

	@SuppressWarnings("unchecked")
	@Override
	public Collection<Person> getRootPersonBySubordinate() {
		Criteria criteria = this.getSession().createCriteria(Person.class);
    	criteria.add(Restrictions.eq("id", AppContext.getAccountContext().currentUser().getId()));
    	return criteria.list();
	}

	@Override
	public void saveCenterTable(Person person) throws SQLException {
		ShareEim se=this.getShareEimService().getCode("se01");
		if (se!=null) {
			ClassName=se.getClassName();
			userName=se.getUserName();
			userPass=se.getUserPass();
			address=se.getIp();
			baseName=se.getBaseName();
			url1="jdbc:mysql://"+address+":3306/"+baseName;
		}
			try {
				Class.forName(ClassName);
				try {
					con1 = DriverManager.getConnection(url1, userName, userPass);
				} catch (SQLException e) {
					e.printStackTrace();
				}
				try {
					stmt = this.con1.createStatement();
					stmt1 = this.con1.createStatement();
					stmt2 = this.con1.createStatement();
					stmt3 = this.con1.createStatement();
					stmt4 = this.con1.createStatement();
					stmt5 = this.con1.createStatement();
					stmt6 = this.con1.createStatement();
					stmt7 = this.con1.createStatement();
				} catch (SQLException e) {
					e.printStackTrace();
				}
//				String sql = "insert into person(loginCode,loginPwd,resume_id,role_id,dept_id) values ("+"'"+person.getLoginCode()+"'"+","+"'"+person.getLoginPwd()+"'"+","+"'"+person.getResume().getId()+"'"+","+"'"+person.getRole().getId()+"'"+","+"'"+person.getDept().getId()+"'"+")";
//				System.out.println(sql);
//				String sql1 = "insert into hr_person(loginCode,loginPwd) values ("+"'"+person.getLoginCode()+"'"+","+"'"+person.getLoginPwd()+"'"+")";
//				stmt.executeUpdate(sql);
				rs4=stmt1.executeQuery("select * from hr_person where loginCode='"+person.getLoginCode()+"'");
				if (!rs4.next()) {
					rs=stmt2.executeQuery("select * from hr_role where name='"+person.getRole().getName()+"'");
					if (rs.next()) {
						System.out.println(rs.getString("id"));
						String sql1 = "insert into hr_person(loginCode,loginPwd,role_id,personcode) values ("+"'"+person.getLoginCode()+"'"+","+"'"+person.getLoginPwd()+"'"+","+"'"+rs.getString("id")+"','"+person.getPersoncode()+"')";
						stmt3.executeUpdate(sql1);
						rs3=stmt4.executeQuery("select * from hr_person where loginCode='"+person.getLoginCode()+"'");
						while (rs3.next()) {
							String ids=rs3.getString("id");
							rs1=stmt5.executeQuery("select * from hr_dept where name='"+person.getDept().getName()+"'");
							while (rs1.next()) {
								String sql2 = "update hr_person as p set p.dept_id="+"'"+rs1.getString("id")+"'"+"where id="+"'"+ids+"'";
								stmt6.executeUpdate(sql2);
								rs2=stmt7.executeQuery("select * from hr_resume where name='"+person.getResume().getName()+"'");
								while (rs2.next()) {
									String ids1=rs2.getString("id");
									String sql3 = "update hr_person set resume_id='"+ids1+"' where id='"+ids+"'";
									stmt.executeUpdate(sql3);
								}
								rs2.close();
							}
							rs1.close();
						}	
						rs3.close();
					}
					rs.close();
				}
				rs4.close();
				con1.close();
			} catch (ClassNotFoundException e) {	
				e.printStackTrace();
			}
		}
	}